package anbook.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import anbook.dao.IFriendDAO;
import anbook.dao.IMemberDAO;
import anbook.db.Database;
import anbook.dto.Friend;
import anbook.dto.Friends_request;
import anbook.dto.Member;

public class MemberDAOImpl implements IMemberDAO, IFriendDAO{
	
	@Override
	public boolean insertMember(Member member) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		boolean ret=true;
		
		try{
			con = Database.Open();
			
			String sql = SQL_INSERT_MEMBER;
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, member.getMb_email());
			pstmt.setString(2, member.getMb_password());
			pstmt.setDate(3, member.getMb_birth());
			pstmt.setString(4, member.getMb_sex());
			pstmt.setString(5, member.getMb_nicname());
			pstmt.setString(6, member.getMb_local());
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
			ret = false;
		}finally{
			Database.Close(con, pstmt, null);
		}
		
		return ret;
	}

	@Override
	public void removeMember(String email) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_DELETE_MEMBER;
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, email);
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void updateMember(Member member) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_UPDATE_MEMBER;
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, member.getMb_password());
			pstmt.setDate(2, member.getMb_birth());
			pstmt.setString(3, member.getMb_sex());
			pstmt.setString(4, member.getMb_profile());
			pstmt.setString(5, member.getMb_nicname());
			pstmt.setString(6, member.getMb_local());
			pstmt.setString(7, member.getMb_email());
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public Member getMember(String email) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		Member member = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_SELECT_MEMBER;
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, email);
			
			rs = pstmt.executeQuery();
			
			if (rs.next()){
				member = new Member();
				member.setMb_email(rs.getString("mb_email"));
				member.setMb_password(rs.getString("mb_password"));
				member.setMb_profile(rs.getString("mb_profile"));
				member.setMb_birth(rs.getDate("mb_birth"));
				member.setMb_local(rs.getString("mb_local"));
				member.setMb_nicname(rs.getString("mb_nicname"));
				member.setMb_sex(rs.getString("mb_sex"));
				member.setMb_date(rs.getTimestamp("mb_date"));
			}
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		return member;
	}

	@Override
	public List<String> findEmail(String nicname) {
			Connection con = null;
			PreparedStatement pstmt = null;
			ResultSet rs = null;
			List<String> list = null;
			String email = null;
			
			try{
				con = Database.Open();
				String sql = SQL_FIND_ID;
				pstmt = con.prepareStatement(sql);
				pstmt.setString(1, nicname);
				rs = pstmt.executeQuery();
				
				if (rs.next()){
					list = new ArrayList<String>();
					do{
						email = rs.getString(1);
						list.add(email);
					}while(rs.next());
				}
			}catch(Exception e){
				e.printStackTrace();
			}finally{
				Database.Close(con, pstmt, rs);
			}
			return list;
	}

	@Override
	public String findPassword(String email,  String nicname) {
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String password = null;

		try{
			con = Database.Open();
			String sql = SQL_FIND_PASS;
			pstmt = con.prepareStatement(sql);
			pstmt.setString(1, email);
			pstmt.setString(2, nicname);
			rs = pstmt.executeQuery();
			
			if(rs.next()){
				password = rs.getString(1);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		
		return password;
	}

	@Override
	public void insertFriend(Friend friend) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_INSERT_FRIEND;
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, friend.getMe_email());
			pstmt.setString(2, friend.getFriend_email());
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void deleteFriend(Friend friend) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_DELETE_FRIEND;
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, friend.getMe_email());
			pstmt.setString(2, friend.getFriend_email());
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void insertFriendRequest(Friends_request request) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_INSERT_FRIENDREQUEST;
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, request.getRequest_email());
			pstmt.setString(2, request.getFriend_email());
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public void deleteFriendRequest(Friends_request request) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		
		try{
			con = Database.Open();
			
			String sql = SQL_DELETE_FRIENDREQUEST;
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, request.getRequest_email());
			pstmt.setString(2, request.getFriend_email());
			
			pstmt.execute();
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, null);
		}
	}

	@Override
	public boolean checkFriendRequest(Friends_request request) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean ret = true;
		
		try{
			con = Database.Open();
			
			if (getMember(request.getFriend_email()) == null)
				ret = false;
			else if (request.getFriend_email().equals(request.getRequest_email()))
				ret = false;
			
			String sql = SQL_GET_FRIENDREQUEST;
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, request.getRequest_email());
			pstmt.setString(2, request.getFriend_email());
			
			rs = pstmt.executeQuery();
			
			if (rs.next())
				ret = false;
			
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		
		return ret;
	}

	@Override
	public List<Friends_request> getFriendsRequestList(String email) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		List<Friends_request> list = new ArrayList<Friends_request>();
		
		try{
			con = Database.Open();
			
			String sql = SQL_GET_FRIENDREQUEST_LIST;
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, email);
			
			rs = pstmt.executeQuery();
			
			while (rs.next()){
				Friends_request request = new Friends_request();
				request.setFriend_email(rs.getString("friend_email"));
				request.setRequest_email(rs.getString("request_email"));
				list.add(request);
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		
		return list;
	}

	@Override
	public boolean checkFriend(Friend friend) {
		// TODO Auto-generated method stub
		Connection con = null;
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		boolean ret = true;
		
		try{
			con = Database.Open();
			
			if (getMember(friend.getFriend_email()) == null)
				ret = false;
			
			if (friend.getFriend_email().equals(friend.getMe_email()))
				ret = false;
			
			String sql = SQL_GET_FRIEND;
			pstmt = con.prepareStatement(sql);
			
			pstmt.setString(1, friend.getMe_email());
			pstmt.setString(2, friend.getFriend_email());
			
			rs = pstmt.executeQuery();
			
			if (rs.next()){
				ret = false;
			}
			
		}catch(Exception e){
			e.printStackTrace();
		}finally{
			Database.Close(con, pstmt, rs);
		}
		
		return ret;
	}	
}
